package hotel.discard;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import hotel.common.JDBC;
import hotel.modal.Employee;

public class EmployeeSql {
	protected static Connection conn = null;
	protected static Statement stmt = null;
	protected static ResultSet rs = null;

	// 获取所有员工信息
	public static ArrayList<Employee> getAll() {
		String sql;
		ArrayList<Employee> data = new ArrayList<Employee>();
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = "SELECT EmployeeID, Name, Gender, Birthday, "
						+ "Phone, EntryTime, CertID, Status, Address FROM employee";
				rs = stmt.executeQuery(sql);
				String name, gender, phone, certId, address;
				Timestamp entryTime;
				Date birthday;
				int employeeId, status;
				while (rs.next()) {
					String strEmployeeId = rs.getString("EmployeeID");
					if (strEmployeeId == null)
						employeeId = -1;
					else
						employeeId = Integer.parseInt(strEmployeeId.trim());
					name = rs.getString("Name").trim();
					gender = rs.getString("Gender").trim();
					phone = rs.getString("Phone").trim();
					entryTime = rs.getTimestamp("EntryTime");
					certId = rs.getString("CertID").trim();
					birthday = rs.getDate("Birthday");
					status = rs.getInt("Status");
					address = rs.getString("Address").trim();
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
					Employee employee = new Employee(employeeId, name, gender, df.format(birthday), phone,
							df.format(entryTime), certId, status, address);
					data.add(employee);
				}
				stmt.close();
				/*
				 * System.out.println("数据库表Student已有数据" + data.size() + "条");
				 * for (int i = 0; i < data.size(); i++) {
				 * System.out.println(data.get(i).toString()); }
				 */
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			EmployeeSql.closeAll();
		}

		return data;
	}
	
	// 获取员工信息
	public static Employee get(int employeeId) {
		String sql;
		Employee employee = null;
		if(employeeId < 0) return null;
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = "SELECT Name, Gender, Birthday, " + 
						"Phone, EntryTime, CertID, Status, Address FROM "+
						"employee where EmployeeID="+String.valueOf(employeeId);
				rs = stmt.executeQuery(sql);
				String name, gender, phone, certId, address;
				Timestamp entryTime;
				Date birthday;
				int status;
				while (rs.next()) {
					name = rs.getString("Name").trim();
					gender = rs.getString("Gender").trim();
					phone = rs.getString("Phone").trim();
					entryTime = rs.getTimestamp("EntryTime");
					certId = rs.getString("CertID").trim();
					birthday = rs.getDate("Birthday");
					status = rs.getInt("Status");
					address = rs.getString("Address").trim();
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
					employee = new Employee(employeeId, name, gender, df.format(birthday), phone,
							df.format(entryTime), certId, status, address);
				}
				stmt.close();
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			EmployeeSql.closeAll();
		}

		return employee;
	}

	// 更新员工信息
	public static Boolean update(Employee employee) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("update employee set Name='%s', Gender='%s', Birthday='%s', "
					+ "Phone='%s', EntryTime='%s', CertID='%s', Status=%d, " + "Address='%s' where EmployeeID='%s'",
					employee.getName(), employee.getGender(), employee.getBirthday(), employee.getPhone(),
					employee.getEntryTime(), employee.getCertId(), employee.isStatus() ? 1 : 0, employee.getAddress(),
					employee.getEmployeeId());
			System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			EmployeeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}

	// 删除员工
	public static Boolean delete(Employee employee) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("delete from employee where EmployeeID='%s'", employee.getEmployeeId());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			EmployeeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 添加员工信息
	public static Boolean add(Employee employee) {
		String sql;
		int ret = 0;
		if(employee.getEmployeeId() == -1) return false;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("insert into employee(EmployeeID,Name,Gender,Birthday,Phone," + 
						"EntryTime,CertID,Status,Address) values('%s','%s','%s','%s','%s','%s','%s',%d,'%s')",
						employee.getEmployeeId(), employee.getName(), employee.getGender(), employee.getBirthday(), 
						employee.getPhone(), employee.getEntryTime(), employee.getCertId(), 
						employee.isStatus() ? 1 : 0, employee.getAddress());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			EmployeeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}

	// 添加获取下一个Id
	public static int getNextId() {
		String sql;
		int id = -1;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return -1;
			}
			stmt = conn.createStatement();
			sql = "SELECT max(EmployeeID) from employee";
			//System.out.println(sql);
			rs = stmt.executeQuery(sql);
			if(rs.next()) id = rs.getInt(1);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			EmployeeSql.closeAll();
		}
		return id + 1;
	}
	
	// 条件获取员工信息
	public static ArrayList<Employee> getCondSearch(String col, String cond) {
		String sqlCol = "";
		
		if(cond.equals("男")) cond = "M";
		else if(cond.equals("女")) cond = "F";
		else if(cond.equals("在职")) cond = "1";
		else if(cond.equals("离职")) cond = "0";
		
		switch(col) {
		case "员工编号": case "编号":
			sqlCol = "EmployeeID";
			break;
		case "员工姓名": case "姓名":
			sqlCol = "Name";
			break;
		case "员工性别": case "性别":
			sqlCol = "Gender";
			break;
		case "出生日期":
			sqlCol = "Birthday";
			break;
		case "联系电话": case "电话":
			sqlCol = "Phone";
			break;
		case "入职时间":
			sqlCol = "EntryTime";
			break;
		case "身份证号": case "身份证":
			sqlCol = "CertID";
			break;
		case "员工状态": case "状态":
			sqlCol = "Status";
			if(cond.equals("在") || cond.equals("在职")) cond = "1";
			else if(cond.equals("离") || cond.equals("离职")) cond = "0";
			else if(cond.equals("职")) sqlCol = "";
			break;
		case "家庭住址":
			sqlCol = "Address";
			break;
		default:
			sqlCol = "";
				break;
		}
		
		String sql; 
		ArrayList<Employee> data = new ArrayList<Employee>();
		if(sqlCol == null || sqlCol.equals("")) {
			if(cond.equals("")) {
				data = getAll();
				return data;
			}else {
				sql = String.format("select * from employee where CONCAT(EmployeeID,Name,Gender,"+
								"ifnull(Birthday,''),ifnull(Phone,''),EntryTime,CertID,Status,ifnull(Address,'')) like '%%%s%%'", cond);
			}
		}else {
			sql = String.format("select * from employee where %s like '%%%s%%'", sqlCol, cond);
		}
		//System.out.println(sql);
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				String name, gender, phone, certId, address;
				Timestamp entryTime;
				Date birthday;
				int employeeId, status;
				while (rs.next()) {
					String strEmployeeId = rs.getString("EmployeeID");
					if (strEmployeeId == null)
						employeeId = -1;
					else
						employeeId = Integer.parseInt(strEmployeeId.trim());
					name = rs.getString("Name").trim();
					gender = rs.getString("Gender").trim();
					phone = rs.getString("Phone").trim();
					entryTime = rs.getTimestamp("EntryTime");
					certId = rs.getString("CertID").trim();
					birthday = rs.getDate("Birthday");
					status = rs.getInt("Status");
					address = rs.getString("Address").trim();
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
					Employee employee = new Employee(employeeId, name, gender, df.format(birthday), phone,
							df.format(entryTime), certId, status, address);
					data.add(employee);
				}
				stmt.close();
				/*
				 * System.out.println("数据库表Student已有数据" + data.size() + "条");
				 * for (int i = 0; i < data.size(); i++) {
				 * System.out.println(data.get(i).toString()); }
				 */
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			EmployeeSql.closeAll();
		}

		return data;
	}
	
	private static void closeAll() {
		if (rs != null) try { rs.close(); } catch(Exception e) {}
	    if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	    if (conn != null) try { conn.close(); } catch(Exception e) {}
	}
	
	public static java.sql.Date strToDate(String strDate) {
		String str = strDate;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		java.util.Date d = null;
		try {
			d = format.parse(str);
		} catch (Exception e) {
			e.printStackTrace();
		}
		java.sql.Date date = new java.sql.Date(d.getTime());
		return date;
	}

	public static java.sql.Time strToTime(String strDate) {
		String str = strDate;
		SimpleDateFormat format = new SimpleDateFormat("hh:mm:ss");
		java.util.Date d = null;
		try {
			d = format.parse(str);
		} catch (Exception e) {
			e.printStackTrace();
		}
		java.sql.Time time = new java.sql.Time(d.getTime());
		return time;
	}

}
